package com.ten_4_4.hiring.site.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

import com.ten_4_4.hiring.comm.PageModel;
import com.ten_4_4.hiring.comm.util.DBUtil;
import com.ten_4_4.hiring.company.po.PositionPo;
import com.ten_4_4.hiring.site.po.Comment;

public class SiteDao {

	public PageModel<Comment> loadingDataByPaging(int pageNo, int pageSize) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT cy.name as 'company$name', ct.comments ");
		sb.append("FROM company cy, comment ct ");
		sb.append("WHERE cy.id = ct.company_id ");
		sb.append("LIMIT ?, ? ");
		String sql = sb.toString();
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Comment> list = new ArrayList<>();
		PageModel<Comment> pageModel = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, (pageNo - 1) * pageSize);
			ps.setInt(2, pageSize);
			rs = ps.executeQuery();
			while(rs.next()) {
				Comment comment = new Comment();
				comment.setCompany$name(rs.getString("company$name"));
				comment.setComments(rs.getString("comments"));
				list.add(comment);
			}
			pageModel = new PageModel<>(list, pageNo, pageSize, getAllComments());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, conn);
		}
		
		return pageModel;
	}

	private int getAllComments() {
		String sql = "select count(*) from comment";
		return DBUtil.getCount(sql);
	}


}
